package net.easyjava.third.db.base;

import com.alibaba.fastjson.JSONObject;
import net.easyjava.third.db.DBException;
import net.easyjava.third.db.utils.DBCreate;
import net.easyjava.third.db.utils.DBInsert;
import net.easyjava.third.db.utils.DBQuery;
import net.easyjava.third.db.utils.DBUtils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by wxnacy on 16/6/29.
 */
public abstract class DBClient {
    protected static String jdbcUrl;
    protected static String jdbcName;
    protected static String jdbcPassword;
    protected static Connection cvConn;

    protected static void init() throws SQLException {
        if (cvConn == null) {
            cvConn = DriverManager.getConnection(jdbcUrl, jdbcName, jdbcPassword);
        }
    }

    public void destroy() {
        if (this.cvConn != null) {
            try {
                this.cvConn.close();
                this.cvConn = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 插入数据
     * @return
     */
    public boolean insert(Object obj) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, SQLException {
        DBInsert dbInsert = new DBInsert(obj);
        String sql = dbInsert.getSql();
        return execute(sql);
    }

    public <T> boolean  insertBatch(List<T> list){
        try{
            List<String> sqlList = new ArrayList<>();
            for(T t:list){
                DBInsert dbInsert = new DBInsert(t);
                String sql = dbInsert.getSql();
                sqlList.add(sql);
            }
            executeBatch(sqlList);
            return true;
        }catch(Exception e){
            throw new DBException(e);
        }
    }

    /**
     * 创建表格
     * @param clazz
     * @return
     * @throws NoSuchMethodException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws SQLException
     */
    public boolean create(Class clazz) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, SQLException {

        DBCreate dbCreate = new DBCreate(clazz);
        String sql = dbCreate.getSql();

        return execute(sql);
    }


    /**
     * 执行查询活修改操作
     * @param sql
     * @param params 参数集合
     * @return
     */
    public boolean execute(String sql,Object... params) throws SQLException {
        PreparedStatement pstmt = null;
        boolean flag = true;
        pstmt = this.cvConn.prepareStatement(sql);
        DBUtils.putParmasToPreparedStatement(pstmt,params);
        pstmt.execute();
        if(pstmt != null){
            pstmt.close();
        }
        return flag;
    }

    public boolean executeBatch(List<String> sqlList) throws SQLException {
        Statement pstmt = null;
        boolean flag = true;
        pstmt = this.cvConn.createStatement();
        for (String sql:sqlList) {
            pstmt.addBatch(sql);
        }
        pstmt.executeBatch();
        if(pstmt != null){
            pstmt.close();
        }
        return flag;
    }


    /**
     * 查询
     * @param sql
     * @return
     */
    public List<JSONObject> query(String sql,Object... params) throws SQLException {
//        String sql = "SELECT * FROM BANZOU WHERE aliyun_url = ''";
        PreparedStatement pstmt = null;
        List<JSONObject> list = new ArrayList<>();
        pstmt = this.cvConn.prepareStatement(sql);
        DBUtils.putParmasToPreparedStatement(pstmt,params);
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()){
            ResultSetMetaData rsmd = rs.getMetaData();
            int size = rsmd.getColumnCount();
            JSONObject jsonObject = new JSONObject();
            for(int i=1;i<=size;i++){
                String name = rsmd.getColumnName(i);
                String typeName = rsmd.getColumnTypeName(i);
                switch (typeName){
                    case "INT":jsonObject.put(name,rs.getInt(name));
                        break;
                    case "VARCHAR":jsonObject.put(name,rs.getString(name));
                        break;
                    case "BIGINT":jsonObject.put(name,rs.getLong(name));
                        break;
                    default:jsonObject.put(name,rs.getString(name));
                        break;
                }

            }
            list.add(jsonObject);
        }

        if(pstmt != null){
            pstmt.close();
        }
        return list;
    }

    /**
     * 查询并将结果反射为传入的Class
     * @param sql
     * @param clazz
     * @param <T>
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws InvocationTargetException
     * @throws SQLException
     */
    public <T> List<T> query(String sql,Class<T> clazz,Object... params) throws IllegalAccessException, InstantiationException, InvocationTargetException, SQLException, NoSuchMethodException {
        PreparedStatement pstmt = null;
        List<T> list = new ArrayList<>();
        pstmt = this.cvConn.prepareStatement(sql);
        DBUtils.putParmasToPreparedStatement(pstmt,params);
        ResultSet rs = pstmt.executeQuery();
        DBQuery dbQuery = new DBQuery(clazz,rs);
        list = dbQuery.parseArray();
        try {
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
